package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

type AppInfo struct {
	Id         int64
	AppId      string
	AppSecret  string
	DbUrl      string
	DbUser     string
	DbPassword string
	DbDriver   string
}

type ChapterSql struct {
}

func main() {
	db, err := sql.Open("mysql", "root:admin@tcp(localhost:3306)/meiyijia-data-access-layer?charset=utf8")
	if err != nil {
		fmt.Println("打开数据库失败,失败原因:", err.Error())
		panic(err)
		return
	}
	defer func(db *sql.DB) {
		err := db.Close()
		if err != nil {
			fmt.Println("关闭数据库失败,失败原因:", err.Error())
		}
	}(db)
	//增删改
	chapterSql := ChapterSql{}
	//插入
	//done := chapterSql.insertOne(err, db)
	//if done {
	//	return
	//}

	//修改数据
	//done := chapterSql.updateOne(err, db)
	//if done {
	//	return
	//}

	//查询数据
	queryData := chapterSql.queryData(err, db)
	if queryData == nil {
		fmt.Println("查询数据返回nil")
		return
	}
	for _, one := range queryData {
		fmt.Println(one)
	}
	//删除数据
	//done := chapterSql.deleteOne(err, db)
	//if done {
	//	return
	//}
}
func (c *ChapterSql) queryData(err error, db *sql.DB) []AppInfo {
	stmt, err := db.Prepare("select id,app_id from app_info where id <? ")
	if err != nil {
		fmt.Println("预编译 查询 sql失败,失败原因:", err.Error())
		return nil
	}
	rows, err := stmt.Query(5)
	if err != nil {
		fmt.Println(" 查询 sql失败,失败原因:", err.Error())
		return nil
	}
	user := new(AppInfo)
	infos := make([]AppInfo, 0, 0)
	for rows.Next() {
		err := rows.Scan(&user.Id, &user.AppId)
		if err != nil {
			fmt.Println(" 查询获取每行数据失败,失败原因:", err.Error())
			continue
		}

		infos = append(infos, *user)
	}

	defer func(rows *sql.Rows) {
		err := rows.Close()
		if err != nil {
			fmt.Println("关闭查询rows失败,失败原因:", err.Error())
		}
	}(rows)

	return infos
}

func (c *ChapterSql) deleteOne(err error, db *sql.DB) bool {
	stmt, err := db.Prepare("delete from app_info  where id=?")
	if err != nil {
		fmt.Println("预编译 删除 sql失败,失败原因:", err.Error())
		return true
	}
	result, err := stmt.Exec(4)
	if err != nil {
		fmt.Println("删除 sql失败,失败原因:", err.Error())
		return true
	}
	affected, err := result.RowsAffected()
	if err != nil {
		fmt.Println("获取 删除 sql影响的行数失败,失败原因:", err.Error())
		return true
	}
	fmt.Println("获取 删除  sql影响的行数:", affected)
	return false
}

func (c *ChapterSql) updateOne(err error, db *sql.DB) bool {
	stmt, err := db.Prepare("update app_info set app_secret ='go' where id=?")
	if err != nil {
		fmt.Println("预编译update sql失败,失败原因:", err.Error())
		return true
	}
	result, err := stmt.Exec(4)
	if err != nil {
		fmt.Println("修改sql失败,失败原因:", err.Error())
		return true
	}
	affected, err := result.RowsAffected()
	if err != nil {
		fmt.Println("获取修改sql影响的行数失败,失败原因:", err.Error())
		return true
	}
	fmt.Println("获取修改sql影响的行数:", affected)
	return false
}

func (c *ChapterSql) insertOne(err error, db *sql.DB) bool {
	stmt, err := db.Prepare("insert into app_info values (?,?,?,?,?,?,?)")
	if err != nil {
		fmt.Println("预编译sql失败,失败原因:", err.Error())
		return true
	}
	result, err := stmt.Exec(4, "go-sql", "alkjwoieurlkj-sldfalkj", "3306", "root", "admin", "mysql")
	if err != nil {
		fmt.Println("插入sql失败,失败原因:", err.Error())
		return true
	}
	//获取影响的行数
	affected, err := result.RowsAffected()
	//获取插入成功后返回的id
	//result.LastInsertId()
	if err != nil {
		fmt.Println("获取插入sql影响的行数失败,失败原因:", err.Error())
		return true
	}
	fmt.Println("获取插入sql影响的行数:", affected)
	return false
}
